﻿using ProjectScheduling.models;
using SqlSugar;
using System;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;

namespace ProjectScheduling.daos
{
    public class BaseDao<T> where T : BClass, new()
    {
        public SqlSugarClient Db
        {
            get
            {
                return new SqlSugarClient(new ConnectionConfig()
                {
                    //ConnectionString = "Data Source=39.103.220.82;Initial Catalog=ProjectScheduling;Persist Security Info=True;User ID=ld;Password=ldinfo.com123456;Encrypt=True;TrustServerCertificate=True;",
                    //ConnectionString = "Data Source=192.168.0.85;Initial Catalog=ProjectScheduling;Persist Security Info=True;User ID=sa;Password=a123456;Encrypt=True;TrustServerCertificate=True;",
                    ConnectionString= "Data Source=36160yp3nq40.vicp.fun,35031;Initial Catalog=ProjectScheduling;Persist Security Info=True;User ID=sa;Password=a123456;Encrypt=True;TrustServerCertificate=True;",//36160yp3nq40.vicp.fun:35031
                    IsAutoCloseConnection = true,
                    DbType = SqlSugar.DbType.SqlServer,
                    LanguageType = LanguageType.Chinese
                });
            }
        }
        #region 列表查询
        public List<T> FindAll(Expression<Func<T, bool>> func) => Db.Queryable<T>().Where(func).ToList();
        public List<T> FindAll(SearchQuery<T> query) => Search(query).ToList();
        #endregion
        #region 分页列表查询
        public PageData<T> FindAll(int page, int pageSize, Expression<Func<T, bool>> func)
        {
            int total = 0;
            return new PageData<T>
            {
                Items = Db.Queryable<T>().Where(func).ToPageList(page, pageSize, ref total),
                Total = total
            };
        }
        public PageData<T> FindAll(int page, int pageSize, SearchQuery<T> query)
        {
            var list = Search(query);
            int total = 0;
            return new PageData<T>
            {
                Items = list.ToPageList(page, pageSize, ref total),
                Total = total
            };
        }
        /// <summary>
        /// 分页并且排序
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="func">查询</param>
        /// <param name="orderby">排序字段</param>
        /// <param name="isDesc">是否倒叙</param>
        /// <returns></returns>
        public PageData<T> FindAll(int page, int pageSize, Expression<Func<T, bool>> func, Expression<Func<T, object>> orderby, bool isDesc = false)
        {
            var list = Search(new SearchQuery<T>
            {
                Query = func,
                OrderBy = orderby,
                IsDesc = isDesc
            });
            //list = list.Skip((page - 1) * pageSize).Take(pageSize);
            int total = 0;
            return new PageData<T>
            {
                Items = list.ToPageList(page, pageSize, ref total),
                Total = total
            };
        }
        #endregion
        #region 单项结果查询
        public T Find(int id)
        {
            return Find(it => it.Id == id);
        }
        public T Find(Expression<Func<T, bool>> func)
        {
            return Db.Queryable<T>().First(func);
        }
        #endregion

        #region Search方法
        private ISugarQueryable<T> Search(SearchQuery<T> query)
        {
            ISugarQueryable<T> list = Db.Queryable<T>();
            if (query != null)
            {
                if (query.Query != null)
                {
                    list = list.Where(query.Query);
                }
                if (query.OrderBy != null)
                {
                    if (query.IsDesc)
                    {
                        list = list.OrderByDescending(query.OrderBy);
                    }
                    else
                    {
                        list = list.OrderBy(query.OrderBy);
                    }
                }
            }
            return list;
        }
        public List<K> Search<K>(Expression<Func<T, bool>> func, Expression<Func<T, K>> result, params Expression<Func<T, object>>[] includes)
        {
            ISugarQueryable<T> query = Db.Queryable<T>();
            if (includes.Length > 0)
            {
                foreach (var item in includes)
                {
                    query = query.Includes(item);
                }
            }
            return query.Where(func).Select(result).ToList();
        }
        #endregion

        #region 新增
        public int Add(T item)
        {
            return Db.Insertable(item).IgnoreColumns(it => it.Id).ExecuteReturnIdentity();
        }
        public void AddRange(List<T> list)
        {
            Db.Insertable(list).IgnoreColumns(it => it.Id).ExecuteCommand();
        }
        #endregion
        #region 更新
        public int Update(T item)
        {
            return Db.Updateable(item).ExecuteCommand();
        }
        public int Update(T item, Expression<Func<T, object>> fields)
        {
            return Db.Updateable(item).UpdateColumns(fields).ExecuteCommand();
        }
        #endregion
        #region 删除
        public int Delete(int id)
        {
            return Db.Deleteable<T>().Where(it => it.Id == id).ExecuteCommand();
        }
        public int Delete(Expression<Func<T, bool>> func)
        {
            return Db.Deleteable(func).ExecuteCommand();
        }
        #endregion

        #region 分组统计
        public List<T> Group(Expression<Func<T, bool>> func, Expression<Func<T, object>> groupby)
        {
            return Db.Queryable<T>().Where(func).GroupBy(groupby).ToList();
        }
        /// <summary>
        /// 获取最小值
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="func"></param>
        /// <param name="property"></param>
        /// <returns></returns>
        public K GetMin<K>(Expression<Func<T, bool>> func, Expression<Func<T, K>> property)
        {
            return Db.Queryable<T>().Where(func).Min(property);
        }
        public K GetMax<K>(Expression<Func<T, bool>> func, Expression<Func<T, K>> property)
        {
            return Db.Queryable<T>().Where(func).Max(property);
        }
        #endregion

        public bool Exists(Expression<Func<T, bool>> func)
        {
            return Db.Queryable<T>().Any(func);
        }
        public DataTable ToTable(string sql, params (string key, object value)[] pars)
        {
            var p = pars.Select(it => new SugarParameter(it.key, it.value)).ToList();
            return Db.Ado.GetDataTable(sql, p);
        }
        public DataTable ToTable(Expression<Func<T, bool>> func)
        {
            return Db.Queryable<T>().Where(func).ToDataTable();
        }

    }
}
